package com.bsoft.gol.hcs.specialist.dao;

import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.spring.boot.annotation.SsdevDao;
import ctd.util.annotation.RpcService;
import ctd.util.converter.ConversionUtils;
import com.bsoft.gol.hcs.odshmrecord.bean.response.QueryHpiCodeListResponse;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import com.bsoft.gol.hcs.specialist.bean.request.FusCrucialIndexUnionResultRecordsResp;
import com.bsoft.gol.hcs.specialist.bean.request.QueryCrucialIndexRequest;
import com.bsoft.gol.hcs.specialist.entity.FusCrucialIndexRecord;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Description: 关联指标记录dao
 * @Author: zhaorb
 * @Date: 2020/7/20 14:56
 **/
@SsdevDao( serviceId = "fusCrucialIndexServiceDAO")
public abstract class FusCrucialIndexRecordDAO extends HibernateSupportDelegateDAO<FusCrucialIndexRecord> {


    public List<FusCrucialIndexRecord> queryFusCrucialIndexRecordList(final QueryCrucialIndexRequest request) {
        HibernateStatelessResultAction<List<FusCrucialIndexRecord>> action = new AbstractHibernateStatelessResultAction<List<FusCrucialIndexRecord>>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuffer stringBuffer = queryFusCrucialIndexRecordStringBuffer(request);
                SQLQuery sqlQuery = ss.createSQLQuery(stringBuffer.toString());
                if (CommonUtils.isNotEmpty(request.getOrganizationList())) {
                    sqlQuery.setParameterList("organizationList", request.getOrganizationList());
                }
                int first = request.getPageNo() > 0 ? (request.getPageNo() - 1) * request.getPageSize() : 0;
                sqlQuery.setFirstResult(first);
                sqlQuery.setMaxResults(request.getPageSize());
                sqlQuery.addEntity(FusCrucialIndexRecord.class);
                setResult(sqlQuery.list());
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * 查询所有的检验检查关联指标（无分页）
     *
     * @param request
     * @return
     */
    public List<FusCrucialIndexRecord> queryFusCrucialIndexAllRecordList(final QueryCrucialIndexRequest request) {
        HibernateStatelessResultAction<List<FusCrucialIndexRecord>> action = new AbstractHibernateStatelessResultAction<List<FusCrucialIndexRecord>>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuffer stringBuffer = queryFusCrucialIndexRecordStringBuffer(request);
                SQLQuery sqlQuery = ss.createSQLQuery(stringBuffer.toString());
                if (CommonUtils.isNotEmpty(request.getOrganizationList())) {
                    sqlQuery.setParameterList("organizationList", request.getOrganizationList());
                }
                sqlQuery.addEntity(FusCrucialIndexRecord.class);
                setResult(sqlQuery.list());
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<FusCrucialIndexUnionResultRecordsResp> queryCrucialIndexUnionResultRecords(final QueryCrucialIndexRequest request) {
        HibernateStatelessResultAction<List<FusCrucialIndexUnionResultRecordsResp>> action = new AbstractHibernateStatelessResultAction<List<FusCrucialIndexUnionResultRecordsResp>>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuffer stringBuffer = queryFusCrucialIndexUnionResultRecords(request);
                Query query = ss.createSQLQuery(stringBuffer.toString());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map<String, Object>> list = query.list();
                List<FusCrucialIndexUnionResultRecordsResp> fusCrucialIndexUnionResultRecordsResps = new ArrayList<>();
                if (list.size() > 0) {
                    for (Map<String, Object> objectMap : list) {
                        fusCrucialIndexUnionResultRecordsResps.add(ConversionUtils.convert(objectMap, FusCrucialIndexUnionResultRecordsResp.class));

                    }
                    setResult(fusCrucialIndexUnionResultRecordsResps);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public BigInteger queryFusCrucialIndexRecordTotal(final QueryCrucialIndexRequest request) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("select COUNT(0) from ( " + queryFusCrucialIndexRecordStringBuffer(request) + " ) t");
                Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());
                if (CommonUtils.isNotEmpty(request.getOrganizationList())) {
                    sqlQuery.setParameterList("organizationList", request.getOrganizationList());
                }
                List list = sqlQuery.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private StringBuffer queryFusCrucialIndexUnionResultRecords(QueryCrucialIndexRequest request) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT " +
                "fc.index_id as indexId, " +
                "fc.organization_id as organizationId, " +
                "fc.organization_name as organizationName, " +
                "fc.index_type as indexType, " +
                "fc.index_name as indexName," +
                "fc.pinyin_code as pinyinCode, " +
                "fc.index_status as indexStatus, " +
                "fc.index_classify_id as indexClassifyId, " +
                "fc.index_classify_name as indexClassifyName, " +
                "fc.index_code as indexCode, " +
                "fc.index_abbreviation as indexAbbreviation, " +
                "fr.sample_type as sampleType, " +
                "fc.sample_name as sampleName, " +
                "fc.significant_digits as significantDigits, " +
                "fc.description, " +
                "fc.hpi_code as hpiCode, " +
                "fc.hpi_status as hpiStatus, " +
                "fc.index_default as indexDefault, " +
                "fc.create_time as createDt, " +
                "fr.index_result_id as indexResultId, " +
                "fr.index_result_content as indexResultContent, " +
                "fc.index_result_type as indexResultType, " +
//                "fr.gender, " +
                "fr.age_lower_limit as ageLowerLimit, " +
                "fr.age_upper_limit as ageUpperLimit, " +
                "fr.upper_limit as upperLimit, " +
                "fr.lower_limit as lowerLimit, " +
                "fr.index_description as indexDescription, " +
                "fr.index_ids as indexIds, " +
                "fr.create_time as createTime " +
                "FROM fus_crucial_index_record fc " +
                "INNER JOIN fus_index_result fr ON fc.index_id = fr.index_id where 1=1 and fc.index_status= '1' ");
        if (CommonUtils.isNotEmpty(request.getIndexClassifyId())) {
            stringBuffer.append(" and fc.index_classify_id = '" + request.getIndexClassifyId() + "'");
        }
        if (CommonUtils.isNotEmpty(request.getIndexStatus())) {
            stringBuffer.append(" and fc.index_status = '" + request.getIndexStatus() + "'");
        }
        if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
            stringBuffer.append(" and fc.organization_id = '" + request.getOrganizationId() + "'");
        }
        if (CommonUtils.isNotEmpty(request.getIndexName())) {
            stringBuffer.append(" and ( fc.index_name like '" + request.getIndexName() + "%' " +
                    "or fc.pinyin_code like '" + request.getIndexName() + "%' ) ");
        }
        if (CommonUtils.isNotEmpty(request.getPinyinCode())) {
            stringBuffer.append(" and fc.pinyin_code like '" + request.getPinyinCode() + "%' ");
        }
        if (CommonUtils.isNotEmpty(request.getIndexType())) {
            stringBuffer.append(" and fc.index_type = " + request.getIndexType());
        }
        return stringBuffer;
    }


    private StringBuffer queryFusCrucialIndexRecordStringBuffer(QueryCrucialIndexRequest request) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select * from fus_crucial_index_record WHERE 1 =1 ");
        if (CommonUtils.isNotEmpty(request.getIndexClassifyId())) {
            stringBuffer.append(" and index_classify_id = '" + request.getIndexClassifyId() + "'");
        }
        if (CommonUtils.isNotEmpty(request.getIndexStatus())) {
            stringBuffer.append("and index_status = '" + request.getIndexStatus() + "'");
        }
        if (null != request.getOrganizationId()) {
            stringBuffer.append(" and organization_id = '" + request.getOrganizationId() + "'");
        }
        if (CommonUtils.isNotEmpty(request.getOrganizationList())) {
            stringBuffer.append("and organization_id in (:organizationList) ");
        }
        if (CommonUtils.isNotEmpty(request.getIndexName())) {
            stringBuffer.append(" and ( index_name like " + "'%"+request.getIndexName() + "%' " +
                    "or pinyin_code like '" + request.getIndexName() + "%' ) ");
        }
        if (CommonUtils.isNotEmpty(request.getPinyinCode())) {
            stringBuffer.append(" and pinyin_code like '" + request.getPinyinCode() + "%' ");
        }
        if (CommonUtils.isNotEmpty(request.getIndexType())) {
            stringBuffer.append(" and index_type = " + request.getIndexType());
        }

        stringBuffer.append(" order by create_time desc");
        return stringBuffer;
    }

    @DAOMethod(sql = " from FusCrucialIndexRecord where indexId =:indexId ")
    public abstract FusCrucialIndexRecord getFusCrucialIndexRecordByKey(@DAOParam("indexId") String indexId);

    @DAOMethod(sql = " from FusCrucialIndexRecord where indexType =:indexType and indexCode=:indexCode and indexClassifyId=:indexClassifyId ")
    public abstract FusCrucialIndexRecord getFusCrucialIndexRecordByInspect(@DAOParam("indexType") String indexType,
                                                                            @DAOParam("indexCode") String indexCode,
                                                                            @DAOParam("indexClassifyId") String indexClassifyId);

    @RpcService
    @DAOMethod(sql = " from FusCrucialIndexRecord where indexCode =:indexCode ")
    public abstract FusCrucialIndexRecord getFusCrucialIndexRecordByIndexCode(@DAOParam("indexCode") String indexCode);

    @RpcService
    @DAOMethod(sql = "select count(1) from FusCrucialIndexRecord where indexName =:indexName ")
    public abstract Long getCrucialIndexRecordByIndexName(@DAOParam("indexName") String indexName);

    @RpcService
    @DAOMethod(sql = "select count(1) from FusCrucialIndexRecord where indexCode =:indexCode ")
    public abstract Long getCrucialIndexRecordByIndexCode(@DAOParam("indexCode") String indexCode);

    @DAOMethod(sql = "delete from FusCrucialIndexRecord where indexId =:indexId")
    public abstract void deleteByIndexId(@DAOParam("indexId") String indexId);

    @DAOMethod(sql = " from FusCrucialIndexRecord where organizationId =:organizationId and indexType =:indexType" +
            " and indexClassifyId =:indexClassifyId and indexStatus='1' ")
    public abstract List<FusCrucialIndexRecord> queryRecordList(
            @DAOParam("organizationId") String organizationId, @DAOParam("indexClassifyId") String indexClassifyId,
            @DAOParam("indexType") String indexType);

    @DAOMethod(sql = " from FusCrucialIndexRecord where  indexType =:indexType" +
            " and indexClassifyId =:indexClassifyId and indexStatus='1' ")
    public abstract List<FusCrucialIndexRecord> queryRecordList2(@DAOParam("indexClassifyId") String indexClassifyId,
                                                                 @DAOParam("indexType") String indexType);


    @DAOMethod(sql = " from FusCrucialIndexRecord where hpiCode =:hpiCode ")
    public abstract FusCrucialIndexRecord getFusCrucialIndexRecordByHpiCode(@DAOParam("hpiCode") String hpiCode);

    @DAOMethod(sql = " update FusCrucialIndexRecord set index_status =:status where indexId =:indexId ")
    public abstract void updateCrucialIndexStatus(@DAOParam("indexId") String indexId, @DAOParam("status") String status);

    @DAOMethod(sql = " from FusCrucialIndexRecord where  hpiCode != null")
    public abstract List<FusCrucialIndexRecord> queryRecordListByHpiCode();


    @DAOMethod(sql = " select hpiCode from FusCrucialIndexRecord where  hpiCode != null and hpiStatus='1' ")
    public abstract List<String> queryHpiCodeListByHpiStatus();

    public List<QueryHpiCodeListResponse.QuestionnaireBean> queryQuestionnaireList(final String mpiId) {
        HibernateStatelessResultAction<List<QueryHpiCodeListResponse.QuestionnaireBean>> action =
                new AbstractHibernateStatelessResultAction<List<QueryHpiCodeListResponse.QuestionnaireBean>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("SELECT DISTINCT " +
                                " a.doctor_id AS doctorId, " +
                                " a.team_chronic_group_id AS teamChronicGroupId, " +
                                " a.chronic_disease_name AS chronicDiseaseName, " +
                                " a.doc_id AS docId, " +
                                " a.doctor_name AS doctorName, " +
                                " a.org_id AS orgId, " +
                                " a.organization_id AS organizationId, " +
                                " d.form_record_id AS questionnaireId, " +
                                " d.form_name AS questionnaireName " +
                                "FROM " +
                                " fus_team_chronic_group_record a, " +
                                " follow_up_route b, " +
                                " follow_up_route_detail c, " +
                                " follow_up_route_detail_form d " +
                                " " +
                                "WHERE " +
                                " a.chronic_disease_id = b.disease_type_id " +
                                "AND b.route_id = c.route_id " +
                                "AND c.route_detail_id = d.route_detail_id " +
                                "AND c.route_detail_type = '7' " +
                                "AND d.item_type = 1 " +
                                "AND a.person_id =:mpiId and b.status='1'  and a.apply_status='1' ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("mpiId", mpiId);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map<String, Object>> list = query.list();
                        List<QueryHpiCodeListResponse.QuestionnaireBean> questionnaireBeans = new ArrayList<>();
                        if (list.size() > 0) {
                            for (Map<String, Object> objectMap : list) {
                                questionnaireBeans.add(ConversionUtils.convert(objectMap, QueryHpiCodeListResponse.QuestionnaireBean.class));

                            }
                            setResult(questionnaireBeans);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public List<QueryHpiCodeListResponse.QuestionnaireBean> queryQuestionnaireListByArtificial(final String mpiId) {
        HibernateStatelessResultAction<List<QueryHpiCodeListResponse.QuestionnaireBean>> action =
                new AbstractHibernateStatelessResultAction<List<QueryHpiCodeListResponse.QuestionnaireBean>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("SELECT DISTINCT " +
                                " a.doctor_id AS doctorId, " +
                                " a.team_chronic_group_id AS teamChronicGroupId, " +
                                " a.chronic_disease_name AS chronicDiseaseName, " +
                                " a.doc_id AS docId, " +
                                " a.doctor_name AS doctorName, " +
                                " a.org_id AS orgId, " +
                                " a.organization_id AS organizationId, " +
                                " c.form_record_id AS questionnaireId, " +
                                " c.form_name AS questionnaireName " +
                                " FROM " +
                                " fus_team_chronic_group_record a ,follow_up_records b,follow_up_records_extend c "+
                                " WHERE " +
                                " a.team_chronic_group_id = b.team_chronic_group_id and b.record_id=c.record_id " +
                                " AND a.person_id =:mpiId  ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("mpiId", mpiId);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map<String, Object>> list = query.list();
                        List<QueryHpiCodeListResponse.QuestionnaireBean> questionnaireBeans = new ArrayList<>();
                        if (list.size() > 0) {
                            for (Map<String, Object> objectMap : list) {
                                questionnaireBeans.add(ConversionUtils.convert(objectMap, QueryHpiCodeListResponse.QuestionnaireBean.class));

                            }
                            setResult(questionnaireBeans);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public Map<String, Object> queryResultMap(final String indexCode, final String diseaseTypeId) {
        HibernateStatelessResultAction<Map<String, Object>> action =
                new AbstractHibernateStatelessResultAction<Map<String, Object>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("SELECT " +
                                " c.result,a.indexLowerLimit,a.indexUpperLimit,a.index_result_type as indexResultType " +
                                " FROM " +
                                " fus_disease_relate_list a, " +
                                " fus_crucial_index_record b, " +
                                " fus_answer_inspection_report_info c " +
                                " WHERE " +
                                " a.disease_type_id = c.disease_type_id " +
                                " AND b.index_id = a.relate_record_id " +
                                " AND a.relate_type = '4' and b.index_code = c.item_id" +
                                " and b.index_code=:indexCode and c.disease_type_id=:diseaseTypeId");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("indexCode", indexCode);
                        query.setParameter("diseaseTypeId", diseaseTypeId);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map<String, Object>> list = query.list();
                        if (list.size() > 0) {
                            setResult(list.get(0));

                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


}
